﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;
using System.Data;



namespace DAO
{
    public class HoSoDaiLyDAO
    {
        #region 1.Inserting
        public static bool InsertHoSoDaiLy(HoSoDaiLyDTO hoSoDaiLyDTO)
        {

            bool isSucessful = false;

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@MaHoSoDaiLy", hoSoDaiLyDTO.MaHoSoDaiLy));
            sqlParams.Add(new SqlParameter("@TenDaiLy", hoSoDaiLyDTO.TenDaiLy));
            sqlParams.Add(new SqlParameter("@TienNo", hoSoDaiLyDTO.TienNo));
            sqlParams.Add(new SqlParameter("@MaLoaiDaiLy", hoSoDaiLyDTO.MaLoaiDaiLy));
            sqlParams.Add(new SqlParameter("@MaQuan", hoSoDaiLyDTO.MaQuan));
            sqlParams.Add(new SqlParameter("@NgayTiepNhan", hoSoDaiLyDTO.NgayTiepNhan));
            sqlParams.Add(new SqlParameter("@EMail", hoSoDaiLyDTO.EMail));
            sqlParams.Add(new SqlParameter("@DienThoai", hoSoDaiLyDTO.DienThoai));
            sqlParams.Add(new SqlParameter("@DiaChi", hoSoDaiLyDTO.DiaChi));

            int n = SqlDataConnectHelper.ExecuteNoneQuery("usp_InsertHoSoDaiLy", sqlParams);
            if (n == -1)
            {
                isSucessful = true;
            }

            return isSucessful;
        }
        #endregion

        #region 2.Delecting
        public static bool DeleteHoSoDaiLy(HoSoDaiLyDTO hoSo)
        {
            bool isSucess = false;

            List<SqlParameter> sqlParam = new List<SqlParameter>();
            sqlParam.Add(new SqlParameter("@MaHoSoDaiLy", hoSo.MaHoSoDaiLy));
            int n = SqlDataConnectHelper.ExecuteNoneQuery("usp_DeleteHoSoDaiLy", sqlParam);
            if (n == -1)
            {
                isSucess = true;
            }

            return isSucess;
        }
        #endregion
        #region 3.Updating
        public static bool UpdateHoSo(HoSoDaiLyDTO hoSoDaiLyDTO)
        {
            bool isSucess = false;

            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@MaHoSoDaiLy", hoSoDaiLyDTO.MaHoSoDaiLy));
            sqlParams.Add(new SqlParameter("@TenDaiLy", hoSoDaiLyDTO.TenDaiLy));
            sqlParams.Add(new SqlParameter("@TienNo", hoSoDaiLyDTO.TienNo));
            sqlParams.Add(new SqlParameter("@MaLoaiDaiLy", hoSoDaiLyDTO.MaLoaiDaiLy));
            sqlParams.Add(new SqlParameter("@MaQuan", hoSoDaiLyDTO.MaQuan));
            sqlParams.Add(new SqlParameter("@NgayTiepNhan", hoSoDaiLyDTO.NgayTiepNhan));
            sqlParams.Add(new SqlParameter("@EMail", hoSoDaiLyDTO.EMail));
            sqlParams.Add(new SqlParameter("@DienThoai", hoSoDaiLyDTO.DienThoai));
            sqlParams.Add(new SqlParameter("@DiaChi", hoSoDaiLyDTO.DiaChi));
            int n = SqlDataConnectHelper.ExecuteNoneQuery("usp_UpdateHoSoDaiLy", sqlParams);
            if (n == -1)
            {
                isSucess = true;
            }

            return isSucess;
        }
        #endregion
        #region 4.Retrieving
        public static List<HoSoDaiLyDTO> GetDaiLyTheoQuan(String maQuan)
        {
            List<HoSoDaiLyDTO> list = new List<HoSoDaiLyDTO>();

            List<SqlParameter> listSqlParameter = new List<SqlParameter>();
            listSqlParameter.Add(new SqlParameter("@MaQuan", maQuan));

            DataTable dt = SqlDataConnectHelper.ExecuteQuery("usp_SoDaiLyHienTai", listSqlParameter);
            foreach (DataRow dr in dt.Rows)
            {
                HoSoDaiLyDTO hoSo = new HoSoDaiLyDTO();

                hoSo.MaHoSoDaiLy = dr["MaHoSoDaiLy"].ToString();
                hoSo.TenDaiLy = dr["TenDaiLy"].ToString();
                hoSo.MaQuan = dr["MaQuan"].ToString();
                hoSo.NgayTiepNhan = DateTime.Parse(dr["NgayTiepNhan"].ToString());
                hoSo.TienNo = float.Parse(dr["TienNo"].ToString());
                hoSo.DiaChi = dr["DiaChi"].ToString();
                hoSo.DienThoai = dr["DienThoai"].ToString();
                hoSo.EMail = dr["Email"].ToString();
                hoSo.MaLoaiDaiLy = dr["MaLoaiDaiLy"].ToString();

                list.Add(hoSo);
            }


            return list;
        }
        public static bool CheckMaHoSoDaiLyExits(String maHoSoDaiLy)
        {
            bool resultExits = false;
            List<HoSoDaiLyDTO> listHoSoDaiLyDTO = new List<HoSoDaiLyDTO>();

            List<SqlParameter> listSqlParameter = new List<SqlParameter>();
            listSqlParameter.Add(new SqlParameter("@Mahosodaily", maHoSoDaiLy));
            DataTable dt = SqlDataConnectHelper.ExecuteQuery("usp_SelectHoSoDaiLy", listSqlParameter);
            if (dt.Rows.Count == 1)
            {
                resultExits = true;
            }


            return resultExits;
        }
        public static List<HoSoDaiLyDTO> SellectByID(String maHoSoDaiLy)
        {
            List<HoSoDaiLyDTO> listHoSoDaiLyDTO = new List<HoSoDaiLyDTO>();

            List<SqlParameter> listSqlParameter = new List<SqlParameter>();
            listSqlParameter.Add(new SqlParameter("@Mahosodaily", maHoSoDaiLy));
            DataTable dt = SqlDataConnectHelper.ExecuteQuery("usp_SelectHoSoDaiLy", listSqlParameter);
            DataRow dr;

            dr = dt.Rows[0];

            HoSoDaiLyDTO hoSo = new HoSoDaiLyDTO();

            hoSo.MaHoSoDaiLy = dr["MaHoSoDaiLy"].ToString();
            hoSo.TenDaiLy = dr["TenDaiLy"].ToString();
            hoSo.MaQuan = dr["MaQuan"].ToString();
            hoSo.NgayTiepNhan = DateTime.Parse(dr["NgayTiepNhan"].ToString());
            hoSo.TienNo = float.Parse(dr["TienNo"].ToString());
            hoSo.DiaChi = dr["DiaChi"].ToString();
            hoSo.DienThoai = dr["DienThoai"].ToString();
            hoSo.EMail = dr["Email"].ToString();
            hoSo.MaLoaiDaiLy = dr["MaLoaiDaiLy"].ToString();

            listHoSoDaiLyDTO.Add(hoSo);


            return listHoSoDaiLyDTO;
        }
        public static List<HoSoDaiLyDTO> SelectAllHoSoDaiLyAll()
        {
            List<HoSoDaiLyDTO> list = new List<HoSoDaiLyDTO>();

            DataTable dt = SqlDataConnectHelper.ExecuteQuery("usp_SelectHoSoDaiLiesAll");
            foreach (DataRow dr in dt.Rows)
            {
                HoSoDaiLyDTO hoSo = new HoSoDaiLyDTO();

                hoSo.MaHoSoDaiLy = dr["MaHoSoDaiLy"].ToString();
                hoSo.TenDaiLy = dr["TenDaiLy"].ToString();
                hoSo.MaQuan = dr["MaQuan"].ToString();
                hoSo.NgayTiepNhan = DateTime.Parse(dr["NgayTiepNhan"].ToString());
                hoSo.TienNo = float.Parse(dr["TienNo"].ToString());
                hoSo.DiaChi = dr["DiaChi"].ToString();
                hoSo.DienThoai = dr["DienThoai"].ToString();
                hoSo.EMail = dr["Email"].ToString();
                hoSo.MaLoaiDaiLy = dr["MaLoaiDaiLy"].ToString();

                list.Add(hoSo);
            }

            return list;
        }
        public static List<HoSoDaiLyDTO> SellectHoSoDaiLyByLoaiDaiLyAndMaQuan(string maLoaiDaiLy, string maQuan)
        {
            List<HoSoDaiLyDTO> listHoSoDaiLyDTO = new List<HoSoDaiLyDTO>();

            List<SqlParameter> SqlPagram = new List<SqlParameter>();
            SqlPagram.Add(new SqlParameter("@MaLoaiDaiLy", maLoaiDaiLy));
            SqlPagram.Add(new SqlParameter("@MaQuan", maQuan));

            DataTable dt = SqlDataConnectHelper.ExecuteQuery("usp_SelectHoSoDaiLiesByAndMaLoaiDaiLyAndMaQuan", SqlPagram);
            foreach (DataRow dr in dt.Rows)
            {
                HoSoDaiLyDTO hoSoDL_DTO = new HoSoDaiLyDTO();

                hoSoDL_DTO.DiaChi = dr["DiaChi"].ToString();
                hoSoDL_DTO.DienThoai = dr["DienThoai"].ToString();
                hoSoDL_DTO.EMail = dr["EMail"].ToString();
                hoSoDL_DTO.MaHoSoDaiLy = dr["mahosodaily"].ToString();
                hoSoDL_DTO.MaLoaiDaiLy = dr["MaLoaiDaiLy"].ToString();
                hoSoDL_DTO.MaQuan = dr["MaQuan"].ToString();
                hoSoDL_DTO.NgayTiepNhan = DateTime.Parse(dr["NgayTiepNhan"].ToString());
                hoSoDL_DTO.TenDaiLy = dr["TenDaiLy"].ToString();

                hoSoDL_DTO.TienNo = float.Parse(dr["TienNo"].ToString());

                listHoSoDaiLyDTO.Add(hoSoDL_DTO);
            }



            return listHoSoDaiLyDTO;

        }
        #endregion
        #region 5.Find
        public static List<HoSoDaiLyDTO> FindHoSoDaiLy(TimKiemDaiLyDTO daiLyCondition)
        {
            List<HoSoDaiLyDTO> hoSoDaiLyDTO = new List<HoSoDaiLyDTO>();

            List<SqlParameter> SqlPagram = new List<SqlParameter>();
            SqlPagram.Add(new SqlParameter("@MaLoaiDaiLy", daiLyCondition.MaLoaiDaiLy));
            SqlPagram.Add(new SqlParameter("@MaQuan", daiLyCondition.MaQuan.Trim()));
            SqlPagram.Add(new SqlParameter("@TenDaiLy", daiLyCondition.TenDaiLy));
            SqlPagram.Add(new SqlParameter("@TienNoMin", daiLyCondition.TienNoMin));
            SqlPagram.Add(new SqlParameter("@tiennomax", daiLyCondition.TienNoMax));
            SqlPagram.Add(new SqlParameter("@ngaytiepnhanBegin", daiLyCondition.NgayTiepNhanBegin));
            SqlPagram.Add(new SqlParameter("@ngaytiepnhanEnd", daiLyCondition.NgayTiepNhanEnd));
            SqlPagram.Add(new SqlParameter("@Email", daiLyCondition.EMail));
            SqlPagram.Add(new SqlParameter("@dienthoai", daiLyCondition.DienThoai));
            SqlPagram.Add(new SqlParameter("@mahosodaily", daiLyCondition.MaHoSoDaiLy));
            SqlPagram.Add(new SqlParameter("@Diachi", daiLyCondition.DiaChi));

            DataTable dt = SqlDataConnectHelper.ExecuteQuery("usp_FindHoSoDaiLy", SqlPagram);
            foreach (DataRow dr in dt.Rows)
            {
                HoSoDaiLyDTO hoSoDL_DTO = new HoSoDaiLyDTO();

                hoSoDL_DTO.DiaChi = dr["DiaChi"].ToString();
                hoSoDL_DTO.DienThoai = dr["DienThoai"].ToString();
                hoSoDL_DTO.EMail = dr["EMail"].ToString();
                hoSoDL_DTO.MaHoSoDaiLy = dr["mahosodaily"].ToString();
                hoSoDL_DTO.MaLoaiDaiLy = dr["MaLoaiDaiLy"].ToString();
                hoSoDL_DTO.MaQuan = dr["MaQuan"].ToString();
                hoSoDL_DTO.NgayTiepNhan = DateTime.Parse(dr["NgayTiepNhan"].ToString());
                hoSoDL_DTO.TenDaiLy = dr["TenDaiLy"].ToString();
                hoSoDL_DTO.TienNo = float.Parse(dr["TienNo"].ToString());

                hoSoDaiLyDTO.Add(hoSoDL_DTO);
            }

            return hoSoDaiLyDTO;
        }
        #endregion

    }
}
